package com.stlm2.util;



import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 封装对excel的操作，包括本地读写excel和流中输出excel 有参构造函数参数为excel的全路径
 * 
 */
public class ExcelUtils {

	// excel文件路径
	private String path = "";

	/**
	 * 无参构造函数 默认
	 */
	public ExcelUtils() {

	}

	/**
	 * 有参构造函数
	 * 
	 * @param path
	 *            excel路径
	 */
	public ExcelUtils(String path) {
		this.path = path;
	}

	/**
	 * 在磁盘生成一个含有内容的excel,路径为path属性
	 * 
	 * @param sheetName
	 *            导出的sheet名称
	 * @param fieldName
	 *            列名数组
	 * @param data
	 *            数据组
	 * @throws IOException
	 */
	public void makeExcel(String sheetName, String[] fieldName, List data, List counts) throws IOException {
		// 在内存中生成工作薄
		HSSFWorkbook workbook = makeWorkBook(sheetName, fieldName, data, counts);
		// 截取文件夹路径
		String filePath = path.substring(0, path.lastIndexOf("\\"));
		// 如果路径不存在，创建路径
		File file = new File(filePath);
		// System.out.println(path+"-----------"+file.exists());
		if (!file.exists())
			file.mkdirs();
		FileOutputStream fileOut = new FileOutputStream(path);
		workbook.write(fileOut);
		fileOut.close();
	}

	/**
	 * 在输出流中导出excel
	 * 
	 * @param excelName
	 *            导出的excel名称 包括扩展名
	 * @param sheetName
	 *            导出的sheet名称
	 * @param fieldName
	 *            列名数组
	 * @param data
	 *            数据组
	 * @param counts 
	 * @param response
	 *            response
	 */
	public static void makeStreamExcel(String excelName, String sheetName, String[] fieldName, List data, List counts, HttpServletResponse response) {
		OutputStream os = null;
		try {
			response.reset(); // 清空输出流
			os = response.getOutputStream(); // 取得输出流
            response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(excelName, "UTF-8") + ".xls"); // 设定输出文件头
			response.setContentType("application/msexcel"); // 定义输出类型
		} catch (IOException ex) {// 捕捉异常
			System.out.println("流操作错误:" + ex.getMessage());
		}
		// 在内存中生成工作薄
		HSSFWorkbook workbook = makeWorkBook(sheetName, fieldName, data, counts);
		try {
			os.flush();
			workbook.write(os);
		} catch (IOException e) {
			e.printStackTrace();
			System.out.println("Output is closed");
		}
		finally {
			try {
				os.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	

	/**
	 * 根据条件，生成工作薄对象到内存
	 * 
	 * @param sheetName
	 *            工作表对象名称
	 * @param fieldName
	 *            首列列名称
	 * @param data
	 *            数据
	 * @param counts 
	 * @return HSSFWorkbook
	 */
	public static HSSFWorkbook makeWorkBook(String sheetName, String[] fieldName, List<Object[]> data, List<Object[]> counts) {
		// 产生工作薄对象
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 产生工作表对象
		HSSFSheet sheet = workbook.createSheet();
		sheet.setDefaultRowHeightInPoints(25);
		sheet.setDefaultColumnWidth(16);
		// 为了工作表能支持中文,设置字符集为UTF_16
		workbook.setSheetName(0, sheetName);
		// 产生一行
		HSSFRow row = sheet.createRow(0);
		// 产生单元格
		HSSFCell cell;
		//设置样式 
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
        HSSFDataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("@"));

        // 写入各个字段的名称
		for (int i = 0; i < fieldName.length; i++) {
			// 创建第一行各个字段名称的单元格
			cell = row.createCell(i);
			// 为了能在单元格中输入中文,设置字符集为UTF_16
			// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			// 给单元格内容赋值
            cell.setCellStyle(style);
            cell.setCellValue(new HSSFRichTextString(fieldName[i]));
		}
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		HSSFFont font2 = workbook.createFont();
		font2.setFontHeightInPoints((short) 11);
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		style2.setFont(font2);
        style2.setDataFormat(format.getFormat("@"));
		// 写入各条记录,每条记录对应excel表中的一行
		for (int i = 0; i < data.size(); i++) {
			Object[] tmp = data.get(i);
			// 生成一行
			row = sheet.createRow(i + 1);
			for (int j = 0; j < tmp.length; j++) {
				cell = row.createCell(j);
				
				if(tmp[j] == null){
					cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
				} else if(tmp[j] instanceof Boolean){
					cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
					cell.setCellValue((Boolean)tmp[j]);
				}else if(tmp[j] instanceof Number){
					Number value = (Number)tmp[j];
					cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellValue(value.doubleValue());
				}else{
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(String.valueOf(tmp[j]));
				}
				cell.setCellStyle(style2);
			}
		}
		//写入统计数据
		if (counts != null) {
			int rowNum = sheet.getLastRowNum();
			for (int i = 0; i < counts.size(); i++) {
				Object[] temp = counts.get(i);
				row = sheet.createRow(rowNum + i + 2);
				cell = row.createCell(0);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(temp[0] == null ? "" : String.valueOf(temp[0]));
				cell.setCellStyle(style);
				
				cell = row.createCell(1);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(temp[1] == null ? "" : String.valueOf(temp[1]));
				cell.setCellStyle(style2);
			}
		}
		return workbook;
		
	}

	public void write(int sheetOrder, int colum, int row, String content) throws Exception {
		Workbook workbook = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(path)));
		Sheet sheet = workbook.getSheetAt(sheetOrder);
		Row rows = sheet.createRow(row);
		Cell cell = rows.createCell(colum);
		cell.setCellValue(content);
		FileOutputStream fileOut = new FileOutputStream(path);
		workbook.write(fileOut);
		fileOut.close();

	}

	/**
	 * 得到一个工作区最后一条记录的序号
	 * 
	 * @param sheetOrder
	 *            工作区序号
	 * @return int
	 * @throws IOException
	 */
	public int getSheetLastRowNum(int sheetOrder) throws IOException {
		Workbook workbook = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(path)));
		Sheet sheet = workbook.getSheetAt(sheetOrder);
		return sheet.getLastRowNum();
	}

	public String read(int sheetOrder, int colum, int row) throws Exception {
		Workbook workbook = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(path)));
		Sheet sheet = workbook.getSheetAt(sheetOrder);
		Row rows = sheet.getRow(row);
		Cell cell = rows.getCell(colum);
		String content = cell.getStringCellValue();
		return content;
	}

	/**
	 * 根据path属性，在磁盘生成一个新的excel
	 * 
	 * @throws IOException
	 */
	public void makeEmptyExcel() throws IOException {
		Workbook wb = new HSSFWorkbook();
		// 截取文件夹路径
		String filePath = path.substring(0, path.lastIndexOf("\\"));
		// 如果路径不存在，创建路径
		File file = new File(filePath);
		if (!file.exists())
			file.mkdirs();
		FileOutputStream fileOut = new FileOutputStream(filePath + "\\" + path.substring(path.lastIndexOf("\\") + 1));
		wb.write(fileOut);
		fileOut.close();
	}

	/**
	 * 根据工作区序号，读取该工作去下的所有记录，每一条记录是一个String[]<br/>
	 * 注意如果单元格中的数据为数字将会被自动转换为字符串<br/>
	 * 如果单元格中存在除数字，字符串以外的其他类型数据，将会产生错误
	 * 
	 * @param sheetOrder
	 *            工作区序号
	 * @return
	 * @throws IOException
	 * @throws
	 */
	public List<String[]> getDataFromSheet(int sheetOrder) throws IOException {
		Workbook workbook = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(path)));
		Sheet sheet = workbook.getSheetAt(sheetOrder);
		List<String[]> strs = new ArrayList<String[]>();
		// 注意得到的行数是基于0的索引 遍历所有的行
		// System.out.println(sheet.getLastRowNum());
		for (int i = 0; i <= sheet.getLastRowNum(); i++) {
			Row rows = sheet.getRow(i);
			String[] str = new String[rows.getLastCellNum()];
			// 遍历每一列
			for (int k = 0; k < rows.getLastCellNum(); k++) {
				Cell cell = rows.getCell(k);
				// 数字类型时
				if (0 == cell.getCellType()) {
					// 用于格式化数字，只保留数字的整数部分
					DecimalFormat df = new DecimalFormat("########");
					str[k] = df.format(cell.getNumericCellValue());
				} else
					str[k] = cell.getStringCellValue();
				// System.out.println(cell.getCellType()+"-------------"+str[k]);
			}
			strs.add(str);
		}
		return strs;
	}
	
	/**
	 * 获得单元格内容
	 * 
	 * @param cell
	 * @return
	 */
	public static String getCellData(Cell cell) {
		String value = "";
		if (cell != null) {
			// 数字类型时
			if (0 == cell.getCellType()) {
				// 用于格式化数字，只保留数字的整数部分
				DecimalFormat df = new DecimalFormat("########");
				value = df.format(cell.getNumericCellValue());
			} else if (2 == cell.getCellType())
				return "";
			else
				value = cell.getStringCellValue();
			value = value.trim();
			if ("/".equals(value))
				value = "";
		}
		return value;
	}

	// 获取邮编
	public static String getCellYB(Cell cell) {
		String value = "";
		if (cell != null) {
			// 数字类型时
			if (0 == cell.getCellType()) {
				// 用于格式化数字，只保留数字的整数部分
				DecimalFormat df = new DecimalFormat("000000");
				value = df.format(cell.getNumericCellValue());
			} else if (2 == cell.getCellType())
				return "";
			else
				value = cell.getStringCellValue();
			value = value.trim();
			if ("/".equals(value))
				value = "";
		}
		return value;
	}
	
	public static List<Object[]> stringList2Object(List<String[]> data){
		List<Object[]> objList = new ArrayList<Object[]>();
		if(data != null && data.size() > 0){
			for(String[] row : data){
				objList.add(row);
			}
		}
		return objList;
	}
}
